﻿/*============================================================================
  File:    Simple Sample AMO Helper.cs

  Summary: Contains the code necessary to build the DataSourceView used in the
           sample and to do the row counting for designing aggregations

           Part of Simple Sample AMO Access

  Date:    November 2006
------------------------------------------------------------------------------
  This file is part of the Microsoft SQL Server Code Samples.

  Copyright (C) Microsoft Corporation.  All rights reserved.

  This source code is intended only as a supplement to Microsoft
  Development Tools and/or on-line documentation.  See these other
  materials for detailed information regarding Microsoft code samples.

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
  KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
  IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

#region Using directives

using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Diagnostics;
using System.Globalization;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.Sql;
using Microsoft.AnalysisServices;

#endregion

namespace Microsoft.Samples.SqlServer
{
    public partial class SimpleSampleAMOAccess
    {

        static void AddTable(DataSourceView dsv, OleDbConnection connection, String tableName)
        {
            Debug.WriteLine( String.Format(  "starting... AddTable(DataSourceView {0}, OleDbConnection \"{1}\", tableName {2})", dsv.Name, connection.ConnectionString, tableName.ToString()));
            string strSelectText = "SELECT * FROM [dbo].[" + tableName + "] WHERE 1=0";
            OleDbDataAdapter adapter = new OleDbDataAdapter(strSelectText, connection);
            DataTable[] dataTables = adapter.FillSchema(dsv.Schema,
                SchemaType.Mapped, tableName);
            DataTable dataTable = dataTables[0];

            dataTable.ExtendedProperties.Add("TableType", "Table");
            dataTable.ExtendedProperties.Add("DbSchemaName", "dbo");
            dataTable.ExtendedProperties.Add("DbTableName", tableName);
            dataTable.ExtendedProperties.Add("FriendlyName", tableName);


            dataTable = null;
            dataTables = null;
            adapter = null;
            Debug.WriteLine(String.Format("ending... AddTable(DataSourceView {0}, OleDbConnection \"{1}\", String {2})", dsv.Name, connection.ConnectionString, tableName.ToString()));
        }

        static void AddComputedColumn(DataSourceView dsv, OleDbConnection connection, String tableName, String computedColumnName, String expression)
        {
            Debug.WriteLine(String.Format("starting... AddComputedColumn(dsv:{0}, connection:\"{1}\", tableName:{2}, computedColumnName:{3}, expression:{4})", dsv.Name, connection.ConnectionString, tableName.ToString(), computedColumnName.ToString(), expression.ToString()));
            DataSet tmpDataSet = new DataSet();
            tmpDataSet.Locale = CultureInfo.CurrentCulture;
            OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT ("
                + expression + ") AS [" + computedColumnName + "] FROM [dbo].["
                + tableName + "] WHERE 1=0", connection);
            DataTable[] dataTables = adapter.FillSchema(tmpDataSet,
                SchemaType.Mapped, tableName);
            DataTable dataTable = dataTables[0];
            DataColumn dataColumn = dataTable.Columns[computedColumnName];

            dataTable.Constraints.Clear();
            dataTable.Columns.Remove(dataColumn);

            dataColumn.ExtendedProperties.Add("DbColumnName", computedColumnName);
            dataColumn.ExtendedProperties.Add("ComputedColumnExpression",
                expression);
            dataColumn.ExtendedProperties.Add("IsLogical", "True");

            dsv.Schema.Tables[tableName].Columns.Add(dataColumn);

            dataColumn = null;
            dataTable = null;
            dataTables = null;
            adapter = null;
            tmpDataSet = null;
            Debug.WriteLine(String.Format("ending... AddComputedColumn(dsv:{0}, connection:\"{1}\", tableName:{2}, computedColumnName:{3}, expression:{4})", dsv.Name, connection.ConnectionString, tableName.ToString(), computedColumnName.ToString(), expression.ToString()));
        }

        static void AddRelation(DataSourceView dsv, String fkTableName, String fkColumnName, String pkTableName, String pkColumnName)
        {
            Debug.WriteLine(String.Format("starting... AddRelation(dsv:{0}, fkTableName:{1}, fkColumnName:{2}, pkTableName:{3}, pkColumnName:{4})", dsv.Name, fkTableName.ToString(), fkColumnName.ToString(), pkTableName.ToString(), pkColumnName.ToString()));
            DataColumn fkColumn
                = dsv.Schema.Tables[fkTableName].Columns[fkColumnName];
            DataColumn pkColumn
                = dsv.Schema.Tables[pkTableName].Columns[pkColumnName];
            dsv.Schema.Relations.Add("FK_" + fkTableName + "_"
                + fkColumnName, pkColumn, fkColumn, true);
            Debug.WriteLine(String.Format("ending... AddRelation(dsv:{0}, fkTableName:{1}, fkColumnName:{2}, pkTableName:{3}, pkColumnName:{4})", dsv.Name, fkTableName.ToString(), fkColumnName.ToString(), pkTableName.ToString(), pkColumnName.ToString()));
        }

        static void AddCompositeRelation(DataSourceView dsv, String fkTableName, String pkTableName, String columnName1, String columnName2)
        {
            Debug.WriteLine(String.Format("starting... AddCompositeRelation(dsv:{0}, fkTableName:{1}, pkTableName:{2}, columnName1:{3}, columnName2:{4})", dsv.Name, fkTableName.ToString(), pkTableName.ToString(), columnName1.ToString(), columnName2.ToString()));
            DataColumn[] fkColumns = new DataColumn[2];
            fkColumns[0] = dsv.Schema.Tables[fkTableName].Columns[columnName1];
            fkColumns[1] = dsv.Schema.Tables[fkTableName].Columns[columnName2];

            DataColumn[] pkColumns = new DataColumn[2];
            pkColumns[0] = dsv.Schema.Tables[pkTableName].Columns[columnName1];
            pkColumns[1] = dsv.Schema.Tables[pkTableName].Columns[columnName2];

            dsv.Schema.Relations.Add("FK_" + fkTableName + "_" + columnName1
                + "_" + columnName2, pkColumns, fkColumns, true);
            Debug.WriteLine(String.Format("ending... AddCompositeRelation(dsv:{0}, fkTableName:{1}, pkTableName:{2}, columnName1:{3}, columnName2:{4})", dsv.Name, fkTableName.ToString(), pkTableName.ToString(), columnName1.ToString(), columnName2.ToString()));
        }

        static DataItem CreateDataItem(DataSourceView dsv, string tableName, string columnName)
        {
            
            Debug.WriteLine(String.Format("starting... CreateDataItem(dsv:{0}, tableName:{1}, columnName:{2}", dsv.Name, tableName.ToString(), columnName.ToString()));
            DataTable dataTable = ((DataSourceView)dsv).Schema.Tables[tableName];
            DataColumn dataColumn = dataTable.Columns[columnName];
            DataItem dataItem= new DataItem(tableName, columnName,
                OleDbTypeConverter.GetRestrictedOleDbType(dataColumn.DataType));
            Debug.WriteLine(String.Format("ending... CreateDataItem(dsv:{0}, tableName:{1}, columnName:{2}", dsv.Name, tableName.ToString(), columnName.ToString()));
            return dataItem;
        }

        static long GetEstimatedNumberOfMembers(DataSourceView dsv, string tableName, string[] columnNames, bool distinctValues)
        {
            Debug.WriteLine(String.Format("starting... GetEstimatedNumberOfMembers(dsv:{0}, tableName:{1}, columnName:{2}, distinctValues:{3}", dsv.Name, tableName, columnNames, distinctValues.ToString()));
            long estimatedNumberOfMembers;

            DataTable table = dsv.Schema.Tables[tableName];
            StringBuilder sb = new StringBuilder();

            sb.Append("SELECT COUNT(*) FROM ( SELECT ");
            if (distinctValues)
                sb.Append("DISTINCT ");

            bool useComma = false;
            foreach (String columnName in columnNames)
            {
                if (useComma)
                    sb.Append(", ");
                else
                    useComma = true;
                sb.Append(columnName);
            }

            sb.Append("  FROM ");
            sb.Append(tableName);
            sb.Append(" ) X");


            // Open a connection to the data source
            OleDbConnection connection
                = new OleDbConnection(dsv.DataSource.ConnectionString);
            connection.Open();

            OleDbCommand countingCommand = connection.CreateCommand();
            countingCommand.CommandText = sb.ToString();
            countingCommand.CommandType = CommandType.Text;

            estimatedNumberOfMembers = long.Parse( countingCommand.ExecuteScalar().ToString());

            connection.Close();
            Debug.WriteLine(String.Format("ending... GetEstimatedNumberOfMembers(dsv:{0}, tableName:{1}, columnName:{2}, distinctValues:{3})= {4}", dsv.Name, tableName, columnNames, distinctValues.ToString(), estimatedNumberOfMembers.ToString()));

            return estimatedNumberOfMembers;
        }


        static long GetEstimatedNumberOfMembers(DataSourceView dsv, DimensionAttribute dimensionAttribute, bool distinctValues)
        {
            Debug.WriteLine(String.Format("starting... GetEstimatedNumberOfMembers(dsv:{0}, dimensionAttribute:{1}, distinctValues:{2}", dsv.Name, dimensionAttribute.Name, distinctValues.ToString()));
            long estimatedNumberOfMembers;
            string tableExpression=null;
            string[] columnExpressions = new string[dimensionAttribute.KeyColumns.Count];
            int i = 0;

            foreach (DataItem column in dimensionAttribute.KeyColumns)
            {
                ColumnBinding cb = (ColumnBinding)column.Source;
                if (cb == null)
                    throw new Exception("Binding not properly set for KeyColumn #" + i + " !");

                if(string.IsNullOrEmpty(tableExpression))
                    tableExpression=GetSqlTableExpression(dsv.Schema, cb, "[", "]"); // use different quotes for different relational datasource providers

                columnExpressions[i++] = GetSqlColumnExpression(dsv.Schema, cb, "[", "]"); // use different quotes for different relational datasource providers
            }

            estimatedNumberOfMembers = GetEstimatedNumberOfMembers(dsv, tableExpression, columnExpressions, true);



            Debug.WriteLine(String.Format("ending... GetEstimatedNumberOfMembers(dsv:{0}, dimensionAttribute:{1}, distinctValues:{2}", dsv.Name, dimensionAttribute.Name, distinctValues.ToString()));

            return estimatedNumberOfMembers;
        }

        static long GetEstimatedNumberOfRows(DataSourceView dsv, string tableName, string filterStament)
        {
            Debug.WriteLine(String.Format("starting... GetEstimatedNumberOfRows(dsv:{0}, tableName:{1}, filterStament:{2}", dsv.Name, tableName, filterStament));
            long estimatedNumberOfRows;
            String countRowsStatement = "SELECT COUNT( * ) FROM " + tableName;

            if (filterStament.Length > 0)
            {
                countRowsStatement += " WHERE " + filterStament;
            }


            // Open a connection to the data source
            OleDbConnection connection
                = new OleDbConnection(dsv.DataSource.ConnectionString);
            connection.Open();

            OleDbCommand countingCommand = connection.CreateCommand();
            countingCommand.CommandText = countRowsStatement;
            countingCommand.CommandType = CommandType.Text;

            estimatedNumberOfRows = long.Parse(countingCommand.ExecuteScalar().ToString());

            connection.Close();
            Debug.WriteLine(String.Format("ending... GetEstimatedNumberOfMembers(dsv:{0}, tableName:{1}, filterStament:{2}", dsv.Name, tableName, filterStament));

            return estimatedNumberOfRows;
        }

        private static string GetSqlTableExpression(DataSet schema, ColumnBinding cb, string openQuote, string closeQuote)
        {
            if ((schema == null) || (cb == null))
                return null;

            DataTable table = schema.Tables[cb.TableID];
            if (table == null)
                return null;

            // We have 2 cases: table or view
            PropertyCollection extendedProperties = table.ExtendedProperties;

            if ((string)extendedProperties["TableType"] == "View") // can be null (not set) or "Table"; in both cases it means table
            {
                string theSqlSelectStatement = (string)extendedProperties["QueryDefinition"];
                if (string.IsNullOrEmpty(theSqlSelectStatement))
                    return null;
                return string.Concat("(", theSqlSelectStatement, ")");
            }
            else
            {
                string dbSchemaName = (string)extendedProperties["DbSchemaName"];
                string dbTableName = (string)extendedProperties["DbTableName"];

                if (string.IsNullOrEmpty(dbTableName))
                    return string.Concat(openQuote, table.TableName, closeQuote);
                else if (string.IsNullOrEmpty(dbSchemaName))
                    return string.Concat(openQuote, dbTableName, closeQuote);
                else
                    return string.Concat(openQuote, dbSchemaName, closeQuote, ".", openQuote, dbTableName, closeQuote);
            }
        }

        private static string GetSqlColumnExpression(DataSet schema, ColumnBinding cb, string openQuote, string closeQuote)
        {
            if ((schema == null) || (cb == null))
                return null;

            DataTable table = schema.Tables[cb.TableID];
            if (table == null)
                return null;

            DataColumn column = table.Columns[cb.ColumnID];
            if (column == null)
                return null;

            PropertyCollection extendedProperties = column.ExtendedProperties;

            if (string.Compare((string)extendedProperties["IsLogical"], bool.TrueString, true, CultureInfo.InvariantCulture) == 0)
            {
                string expression = (string)extendedProperties["ComputedColumnExpression"];
                string dbColumnName = (string)extendedProperties["DbColumnName"];

                if (string.IsNullOrEmpty(expression))
                    return string.Concat(openQuote, string.IsNullOrEmpty(dbColumnName) ? column.ColumnName : dbColumnName, closeQuote);
                else
                    return string.Concat(expression, " AS ", string.Concat(openQuote, column.ColumnName, closeQuote));
            }
            else
            {
                string dbColumnName = (string)extendedProperties["DbColumnName"];

                if (string.IsNullOrEmpty(dbColumnName))
                    return string.Concat(openQuote, column.ColumnName, closeQuote);
                else
                    return string.Concat(openQuote, dbColumnName, closeQuote);
            }
        }


    }
}
